/***********************************************************************  
 *   @package：cn.wj.base.dataBase，@class-name：SimpleJDBC.java   
 *   @copyright     Copyright:   2015-2016     
 *   @creator       wenjie 
 *   @create-time   2016年1月10日   下午8:41:07   
 *   @revision      $Id: 1.0    
 ***********************************************************************/
package cn.wj.base.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import cn.wj.base.util.DBPool;
import cn.wj.base.util.ThreadLocalContext;

public class SimpleJDBC<T> {
	private DBPool dbpool;

	public SimpleJDBC() {
		dbpool = DBPool.getInstance();
	}

	protected Connection getJdbcConnection() {
		return dbpool.getConnection();
	}

	protected void releaseConnection(Connection conn) {
		dbpool.releaseConnection(conn);
	}

	protected List query(String sql, Class clazz, Object... objects) {
		List list = null;
		Connection conn = null;
		try {
			conn = dbpool.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			if (objects.length > 0) {
				for (int i = 0; i < objects.length; i++) {
					ps.setObject(i + 1, objects[i]);
				}
			}
			ResultSet res = ps.executeQuery();
			ResultSetMetaData MDA = res.getMetaData();
			Map<String, Integer> columnLabelMap = new HashMap<String, Integer>();
			for (int i = 0; i < MDA.getColumnCount(); i++) {
				columnLabelMap.put(MDA.getColumnLabel(i + 1), MDA.getColumnType(i + 1));
			}
			list = new ArrayList();
			Object obj = null;
			while (res.next()) {
				obj = clazz.newInstance();
				for (Entry<String, Integer> en : columnLabelMap.entrySet()) {
					Field fiel = clazz.getDeclaredField(en.getKey());
					fiel.setAccessible(true);
					fiel.set(obj, res.getObject(en.getKey()));
				}
				list.add(obj);
			}
			res.close();
			ps.close();
		} catch (IllegalAccessException | IllegalArgumentException | NoSuchFieldException | SQLException
				| InstantiationException e) {
			e.printStackTrace();
		} finally {
			if (conn != null)
				releaseConnection(conn);
		}
		if (list != null && list.size() > 0)
			return list;
		else
			return null;
	}

	protected Object queryForObject(String sql, Class clazz, Object... objects) {
		Object obj = null;
		Connection conn = null;
		try {
			conn = dbpool.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			if (objects.length > 0) {
				for (int i = 0; i < objects.length; i++) {
					ps.setObject(1 + i, objects[i]);
				}
			}
			ResultSet res = ps.executeQuery();
			ResultSetMetaData MDA = res.getMetaData();
			Map<String, Integer> columnLabelMap = new HashMap<String, Integer>();
			for (int i = 0; i < MDA.getColumnCount(); i++) {
				columnLabelMap.put(MDA.getColumnLabel(i + 1), MDA.getColumnType(i + 1));
			}
			if (res.next()) {
				obj = clazz.newInstance();
				for (Entry<String, Integer> en : columnLabelMap.entrySet()) {
					Field fiel = clazz.getDeclaredField(en.getKey());
					fiel.setAccessible(true);
					fiel.set(obj, res.getObject(en.getKey()));
				}
			}
			res.close();
			ps.close();
		} catch (IllegalAccessException | IllegalArgumentException | SQLException | InstantiationException
				| NoSuchFieldException e) {
			e.printStackTrace();
		} finally {
			releaseConnection(conn);
		}
		return obj;
	}

	protected Integer queryForInt(String sql, Object... objects) {
		Integer value = null;
		Connection conn = null;
		try {
			conn = dbpool.getConnection();
			PreparedStatement ps = conn.prepareStatement(sql);
			if (objects.length > 0) {
				for (int i = 0; i < objects.length; i++) {
					ps.setObject(1, objects[i]);
				}
			}
			ResultSet res = ps.executeQuery();
			if (res.next()) {
				value = res.getInt(1);
			}
			res.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			releaseConnection(conn);
		}
		return value;
	}

	protected void delete(String sql, Object columnValue) {
		Connection conn = null;
		try {
			conn = ThreadLocalContext.getInstance().get();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setObject(1, columnValue);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			releaseConnection(conn);
		}
	}

	protected Object update(String sql, List<Object> parmList, Class<?> IdType) {
		Object Id = null;
		Connection conn = null;
		try {
			conn = ThreadLocalContext.getInstance().get();
			PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < parmList.size(); i++) {
				ps.setObject(1 + i, parmList.get(i));
			}
			ps.executeUpdate();
			ResultSet res = ps.getGeneratedKeys();
			if (res.next()) {
				if (IdType == Integer.class) {
					Id = res.getInt(1);
				} else if (IdType == Long.class) {
					Id = res.getLong(1);
				} else
					Id = res.getObject(1);
			}
			res.close();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			releaseConnection(conn);
		}
		return Id;
	}

	protected void update(String sql, List<Object> parmList) {
		Connection conn = null;
		try {
			conn = ThreadLocalContext.getInstance().get();
			PreparedStatement ps = conn.prepareStatement(sql);
			for (int i = 0; i < parmList.size(); i++) {
				ps.setObject(1 + i, parmList.get(i));
			}
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			releaseConnection(conn);
		}
	}

}
